﻿use sms
go
if exists(select name from sysobjects where name ='cp_send_sms' and type = 'P')
drop procedure cp_send_sms
go  
/****** Object:  StoredProcedure [dbo].[cp_send_sms]    Script Date: 01/12/2011 15:17:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[cp_send_sms]
@from nvarchar(50),--输入参数，发件人(可以是Alias,ID,Email)
@text nvarchar(max),---输入参数，短信内容
@to nvarchar(max),---输入参数，收件人
@outs int output--输出参数,返回结果
as
declare @sub varchar(50) ,
		@index int,
		--@countToNumber 传入的@to属于@from的电话本的数量
		@countToNumber int,
		--@@countToContact 传入的@to属于@from的电话本里联系人的数量
		@countToContact int ,
		--自定义错误变量
		@error int,
		--接受查询出来的发件人
		--@addresser nvarchar(20),
		--接受发件人ID
		@userID int
		set @error = 0
select @userID = UserID from [user] where CIL =  @from
--首先判断@to格式是否正确
declare @tos varchar(10)
set @tos = substring(@to,len(@to),len(@to)+1)
if(@tos = ';') 
	begin
		if(@userID != 0)
			begin
				--捕获未知异常
				begin try
						begin
							----批量发送时同一个BatchID,Priority,MaxRetryTime
							--生成一个BatchID
							declare @batchID int,@countToMessage int
							select @countToMessage = COUNT(*) from [Message]
							if(@countToMessage = 0)
								begin
									select @batchID = 1
								end
							else
								begin
									select @batchID = max(BatchID) + 1 from [Message]
								end
							--根据@from查询出Priority和MaxRetryTime		
							declare @priority smallint,@maxRetryTime smallint
							select @priority =  Priority,@maxRetryTime = RetryTime from  Department d
							inner join [user] u on d.DepartmentID = u.DepartmentID
										where u.userID = @userID and u.IsActive = 4
							--循环to
							--开始事务
							BEGIN TRAN 
							while (len(@to)>0)
								begin
									select @index=CHARINDEX(';',@to)
									if(@index>0)
										begin
											---得到@to中的每一个值	
											select @sub=left(@to,@index-1)
											--@countToNumber 传入的@to属于@from的电话本的数量
											select @countToNumber=count(*) from number c
											inner join [contact]  a on c.addressID=a.ID
											inner join [user] u on a.UserID=u.UserID
											where u.userID = @userID and a.Enabled = 1 and a.displayName = @sub or c.number= @sub
											--如果从number里面能查到记录
											if(@countToNumber!=0)
												begin
													--@countToContact 传入的@to属于@from的电话本里联系人的数量
														select @countToContact=count(*) from [contact] a
														inner join [user] u on a.UserID = u.UserID
														where u.userID = @userID and a.Enabled = 1 and a.displayname=@sub
														--@countToContact>0表示属于@from的联系人
															if(@countToContact>0)
																begin
																	print @sub + ' 是from的联系人' 
																				declare  getnumber cursor for
																						select c.number from  number c
																						inner join [contact] a on a.id=c.addressid 
																						inner join [user] u on a.UserID = u.UserID
																						where a.displayName=@sub and u.userID = @userID and a.Enabled = 1
																				open getnumber
																					--@n当前要To的次数
																					--@contactToNumber 根据传入的@to属于@from的电话本里联系人查询出的number
																					declare @n int,@contactToNumber varchar(50) 
																					fetch next from getnumber
																					into @contactToNumber
																					while @@FETCH_STATUS=0      
																						begin 
																						insert [message] values(@batchID,@userID,@contactToNumber,@text,'0',@priority,@maxRetryTime,getdate(),getdate(),'')
																						set @n=@n+1      
																						fetch next from getnumber      
																						into @contactToNumber     
																						end    
																				close getnumber    
																				deallocate getnumber      
																end
															--如果为@from联系人里面的一个电话号码
															else 
																begin
																	declare @number nvarchar(50)
																	select @number=c.number from number c
																	inner join [contact] a on c.addressID=a.ID
																	inner join [user] u on a.UserID=u.UserID
																	where u.userID = @userID and a.Enabled = 1 and a.displayName = @sub or c.number= @sub
																		insert [message] values(@batchID,@userID,@number,@text,'0',@priority,@maxRetryTime,getdate(),getdate(),'')
																	print @number + ' 是@from联系人里面的一个电话号码'
																end
												end
										---如果@countToNumber为0时，就是从number里面不能查到记录
											else
												begin
													--如果isnumeric(@sub)则是一个正确的电话号码，插入一条记录
													if(isnumeric(@sub)=1)
														begin
															print @sub + '不属于联系人，是一个正确的电话号码'
															insert [message] values(@batchID,@userID,@sub,@text,'0',@priority,@maxRetryTime,getdate(),getdate(),'')
															--set @return = ''
														end
													--不是一个正确的电话号码，@error+1
													else
														begin
															set @error=+1
														end	
												end	
											set @to=Ltrim(stuff(@to,1,@index,''))						
										end
									else
										begin
											break
										end
								end
							if(@error != 0)
								--错误的联系人名单或电话号码返回2
								begin
									 ROLLBACK
									 set @outs= 2
								end
							 else
								--完全正确返回0
								begin
									COMMIT
									set @outs= 0
								end	
							--else  
							--	begin
							--		declare @thisSend int,@resigned int,@departmentids int,@maxSends int,@allresigned int
							--		--本次发送
							--		select @thisSend = count(*) from [Message] where BatchID = @batchID
							--		--部门ID
							--		select @departmentids = DepartmentID from [user] where userID = @userID
							--		----已发次数
							--		--select @resigned = CurrentMsg from DepartmentCurrentMsg where DepartmentID = @departmentids
							--		----最大发送次数
							--		--select @maxSends = Limit From Department where DepartmentID = @departmentids
							--		----加本次
							--		--select @allresigned = @resigned + @thisSend
							--		if(@maxSends - @allresigned < 0)
							--			--记录超过返回3
							--			begin
							--				ROLLBACK
							--				set @outs= 3
							--			end
							--		else
							--			--完全正确返回0
							--			begin
							--				COMMIT
							--				set @outs= 0
							--			end	
							--	end
						end
				end try
				--未知异常返回4
				begin catch
					set @outs= 4
				end catch
			end
		else
			begin
				set @outs= 2
			end
		--set @outs= 1
	end
else
	begin
	 set @outs = 1
	end
